/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseproject;

/**
 *
 * @author jesseday
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Performance {
    
    private static Connection con;
    private static String username;
    private static boolean userIsAdmin;
    
    public static void doMenu(Connection nCon, String nUsername, boolean nUserIsAdmin)
    {
        con = nCon;
        username = nUsername;
        userIsAdmin = nUserIsAdmin;
        
        boolean done = false;

        while (!done)
        {
            Menu menu = new Menu();
            menu.setTitle("Performances");
            menu.addOption("List Performances");
            if (userIsAdmin)
            {
                menu.addOption("Add Performance");
                
            }
            else
            {
                menu.addOption("My Shows");
            }
            menu.addOption("Statistical Trivia");
            switch (menu.display())
            {
                case (1):
                    Menu listMenu = new Menu();
                    listMenu.removeBackOption();

                    try{
                        Statement stmt = con.createStatement(
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                        ResultSet rs = stmt.executeQuery("select vname, address, city, st, bname, pdate " 
                                +"from Performance natural join Venue");            
                        
                        rs.first();
                        do
                        {
                            listMenu.addOption(rs.getString("BNAME")+" at "+rs.getString("VNAME")
                                    +" on "+rs.getString("PDATE")); 
           
                        } while (rs.next());
                        
                        int choice = listMenu.display();
                        displayDetail(choice, rs);
                    }
                     catch (java.sql.SQLException e)
                    {
                        return;
                    }
                    
                    
                    break;
                    
                case (2):
                    if(userIsAdmin)
                    {                 
                        Menu venueMenu = new Menu();
                        venueMenu.removeBackOption();

                        Scanner input = new Scanner(System.in);

                        venueMenu.setTitle("Select a Venue");

                        try
                        {
                            Statement stmt = con.createStatement(
                                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                        ResultSet.CONCUR_READ_ONLY);
                            ResultSet rs = stmt.executeQuery("select * from VENUE");

                            rs.first();

                            do{

                               venueMenu.addOption(rs.getString("VNAME") + " " +
                                       rs.getString("CITY") + "," + rs.getString("ST"));

                            }
                            while(rs.next());

                            int choice = venueMenu.display();
                            rs.absolute(choice);

                            Menu bandMenu = new Menu();
                            bandMenu.removeBackOption();
                            bandMenu.setTitle("Select a Band");
                            bandMenu.addOptionsFromSQLQuery(con, "select bname from BAND");

                            int choice2 = bandMenu.display();
                            String band = bandMenu.getOptionAt(choice2);

                            System.out.print("Show Date- Year: ");
                            int year = input.nextInt();
                            System.out.print("          Month: ");
                            int month = input.nextInt();
                            System.out.print("            Day: ");
                            int day = input.nextInt();
                            String date = String.format("%04d-%02d-%02d", year, month, day);

                            System.out.print("Set Duration- Hours: ");
                            int hour = input.nextInt();
                            System.out.print("            Minutes: ");
                            int min = input.nextInt();
                            System.out.print("            Seconds: ");
                            int sec = input.nextInt();
                            String duration = String.format("%02d:%02d:%02d", hour, min, sec);

                            PreparedStatement st2 = con.prepareStatement("insert into PERFORMANCE values (?,?,?,?"+ 
                                    ",'"+date+"','"+duration+"')");
                            st2.setString(1, rs.getString("ADDRESS"));
                            st2.setString(2, rs.getString("city"));
                            st2.setString(3, rs.getString("ST"));
                            st2.setString(4, band);
                            st2.executeUpdate();

                            Menu songMenu = new Menu();
                            songMenu.setTitle("Select a Song (Quit to finish): ");
                            songMenu.removeBackOption();
                            songMenu.addQuitOption();
                            songMenu.addOptionsFromSQLQuery(con, "select sname from SONG");

                            boolean more = true;

                            while(more=true){
                                int songChoice = songMenu.display();
                                String song = songMenu.getOptionAt(songChoice);
                                if(songChoice < songMenu.getQuitOption()){
                                    try
                                    {
                                        PreparedStatement st = con.prepareStatement("insert into PLAYED_AT values (?,?,?,?,?,'" 
                                                + date +"')");
                                        st.setString(1, song);
                                        st.setString(2, rs.getString("ADDRESS"));
                                        st.setString(3, rs.getString("city"));
                                        st.setString(4, rs.getString("ST"));
                                        st.setString(5, band);
                                        st.executeUpdate();
                                    }
                                    catch (java.sql.SQLException e)
                                    {
                                        System.out.println("SQL failed in add song." + e);

                                    }
                                }
                                else{
                                    more = false;
                                    break;
                                }
                            }
                        }
                        catch (java.sql.SQLException e)
                        {
                            return;
                        }
                    }
                    else
                    {
                        myShows();
                    }    
                    
                case(3):
                    stats();
                    break;
                    
                default:
                   done = true;
                   break;

            }
        }
        
      }
   
    
    public static void displayDetail(int selection, ResultSet rs)
    
    {
        try
        {
            rs.absolute(selection);
            
            PreparedStatement stmt1 = con.prepareStatement("select sname from performance natural join"
                                    + " played_at as p where p.address = ?"
                                    + " AND p.city = ?"
                                    + " AND p.st = ?"
                                    + " AND p.bname = ?"
                                    + " AND p.pdate = '" + rs.getDate("PDATE")
                                    + "'",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt1.setString(1,rs.getString("ADDRESS"));
            stmt1.setString(2,rs.getString("CITY"));
            stmt1.setString(3,rs.getString("ST"));
            stmt1.setString(4,rs.getString("BNAME"));
            ResultSet rs1 = stmt1.executeQuery();
            
            boolean allDone = false;
            while(!allDone){
                
                System.out.println("\n" +rs.getString("BNAME") + " at " + rs.getString("VNAME")
                        + " on " + rs.getString("PDATE") + "\n");

                if (rs1.first()){

                    System.out.println("Set List\nSong, [Album]\n");



                    do{

                    PreparedStatement stmt2 = con.prepareStatement("select title from album_song where"
                            + " sname = ?",
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                    stmt2.setString(1, rs1.getString("SNAME"));
                    ResultSet rs2 = stmt2.executeQuery();


                    if(rs2.first())
                        System.out.println(rs1.getString("SNAME") + "   [" + rs2.getString("TITLE") + "]");
                    else
                        System.out.println(rs1.getString("SNAME") + "   [No Album]");

                    }while(rs1.next());

                }
                else
                    System.out.println("No songs were played... riot ensued.");


                PreparedStatement stmt3 = con.prepareStatement("select commenter, comment from performance_com"
                                        + " where address = ?"
                                        + " AND city = ?"
                                        + " AND st = ?"
                                        + " AND bname = ?"
                                        + " AND pdate = '" + rs.getDate("PDATE")
                                        + "'",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                stmt3.setString(1,rs.getString("ADDRESS"));
                stmt3.setString(2,rs.getString("CITY"));
                stmt3.setString(3,rs.getString("ST"));
                stmt3.setString(4,rs.getString("BNAME"));
                ResultSet rs3 = stmt3.executeQuery();

                System.out.println();

                if(rs3.first()){

                    System.out.println("Comments");

                    do{


                        System.out.println(rs3.getString("COMMENTER") + ": " + rs3.getString("COMMENT"));


                    }while(rs3.next());
                }
                else
                    System.out.println("No Comments");

                if(!userIsAdmin)
                {


                    Menu comMenu = new Menu();
                    comMenu.addOption("Add Comment");


                    Scanner input = new Scanner(System.in);
                    switch(comMenu.display()){

                    case(1): 
                        System.out.println();
                        System.out.print(username + ": ");
                        String comment = input.nextLine();

                        PreparedStatement st = con.prepareStatement("insert into PERFORMANCE_COM values (?,?,?,?,'"+
                                rs.getDate("PDATE")+"',?,?)");
                        st.setString(1,rs.getString("ADDRESS"));
                        st.setString(2,rs.getString("CITY"));
                        st.setString(3,rs.getString("ST"));
                        st.setString(4,rs.getString("BNAME"));
                        st.setString(5, username);
                        st.setString(6, comment);
                        st.executeUpdate();
                        break;

                    default:
                        allDone = true;
                        break;
                    }

                }
                else
                {
                    allDone=true;
                }
            }
        }
          
        catch (java.sql.SQLException e)
        {
            System.out.print(e);
            return;
        }
        
    }
    
    public static void myShows()
    {
        try
        {
            boolean doneShows = false;
            while(!doneShows)
            {
                Menu shows = new Menu();
                shows.addOption("Add A Show");
                shows.addOption("View Show Info");
                System.out.println();

                PreparedStatement showSt = con.prepareStatement("select * from attends natural "
                        + "join venue where username = ?",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                showSt.setString(1, username);
                ResultSet showRs = showSt.executeQuery();

                if(showRs.first())
                {
                    System.out.println("My Shows:");
                    do
                    {
                        System.out.println(showRs.getString("BNAME") + " at "
                                + showRs.getString("VNAME") + " on " + showRs.getString("PDATE"));
                    }while(showRs.next());
                }
                else
                {
                    System.out.println("No Shows Attended");
                }

                switch(shows.display())
                {
                    case(1):
                        Menu addShow = new Menu();
                        addShow.setTitle("Select Show:");
                        
                        PreparedStatement notShowSt = con.prepareStatement("select p.address,"
                                + "p.CITY,p.ST,p.BNAME,p.PDATE,vname from performance as p "
                                + "natural join venue where not exists(select * from "
                                + "attends as a where p.ADDRESS=a.ADDRESS and p.CITY=a.CITY "
                                + "and p.ST=a.ST and p.BNAME=a.BNAME and p.PDATE=a.PDATE "
                                + "and username = '"+username+"')",
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                        
                        ResultSet notMyShows = notShowSt.executeQuery();
                        notMyShows.first();
                        
                        do
                        {
                            addShow.addOption(notMyShows.getString("BNAME") + " at "
                                + notMyShows.getString("VNAME") + " on " + notMyShows.getString("PDATE"));
                            
                        }while(notMyShows.next());
                        
                        int option = addShow.display();
                        
                        if(option < addShow.getBackOption())
                        {
                            notMyShows.absolute(option);
                            
                            PreparedStatement addSt = con.prepareStatement("insert into attends values (?,?,?,?,'"
                                    + notMyShows.getString("PDATE") + "',?)");
                            addSt.setString(1,notMyShows.getString("ADDRESS"));
                            addSt.setString(2,notMyShows.getString("CITY"));
                            addSt.setString(3,notMyShows.getString("ST"));
                            addSt.setString(4,notMyShows.getString("BNAME"));
                            addSt.setString(5,username);
                            
                            try {
                                addSt.executeUpdate();
                            }
                            catch (java.sql.SQLException e)
                            {
                                System.out.println(e);
                            }
                        }
                        
                        break;

                    case(2):   
                        if(showRs.first())
                        {
                            Menu showInfo = new Menu();
                            showInfo.setTitle("Select Show:");
                            do
                            {
                                showInfo.addOption(showRs.getString("BNAME") + " at "
                                        + showRs.getString("VNAME") + " on " + showRs.getString("PDATE"));
                            }while(showRs.next());
                            
                            displayDetail(showInfo.display(),showRs);
                        }
                        else
                        {
                            System.out.println("No Shows Attended");
                        }
                        
                        break;

                    default:
                        doneShows = true;
                        break;
                }
            }
        }
        catch (java.sql.SQLException e)
        {
            System.out.print(e);
            return;
        }
    }
    
    public static void stats()
    {
        boolean done = false;
        while(!done)
        {
            Menu menu = new Menu();
            menu.setTitle("Select Band To Display Stats:");
            menu.addOptionsFromSQLQuery(con, "select bname from BAND");
            
            int choice = menu.display();
            
            if(choice<menu.getBackOption())
            {
                String band = menu.getOptionAt(choice);
                try
                {
                    PreparedStatement st = con.prepareStatement("select count(*) from "
                            + "Performance where bname = ? group by bname",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                    st.setString(1, band);
                    //Number of Shows
                    ResultSet rs = st.executeQuery();
                    if(rs.first())
                    {
                        System.out.println("\n"+ band +"'s Statistics:\n" + band + 
                                " has played "+rs.getString(1)+" shows.");
                    }
                    else
                    {
                        System.out.println("\n" + band + " has played no shows.");
                    }
                    
                    //Number of songs played
                    st = con.prepareStatement("select count(*) from "
                            + "played_at where bname = ? group by bname",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                    st.setString(1, band);
                    rs = st.executeQuery();
                    if(rs.first())
                    {
                        System.out.println("\n" + band + " has played "+rs.getString(1)+" songs.");
                    }
                    else
                    {
                        System.out.println("\n" + band + " has played no songs.");
                    }

                    
                    //Number of shows per venue
                    st = con.prepareStatement("select vname, count(*) as concerts "
                            + "from Performance natural join Venue where bname = ? group by vname",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                    st.setString(1, band);
                    rs = st.executeQuery();
                    if(rs.first())
                    {
                        System.out.println("\n" + band + "'s Shows By Venue");
                        do{
                            System.out.println("    " +rs.getString("VNAME") + " - " + 
                                    rs.getString("CONCERTS"));
                        }
                        while(rs.next());
                    }
                    else
                    {
                        System.out.println("");
                    }
                    
                    //Number of Users whove atteded their performances
                    st = con.prepareStatement("select count(*) from performance "
                            + "natural join attends where bname = ? group by bname",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                    st.setString(1, band);
                    rs = st.executeQuery();
                    if(rs.first())
                    {
                        System.out.println("\n" + rs.getString(1) +" users have attended " + band + 
                                "'s shows");
                    }
                    else
                    {
                        System.out.println("\nNo users have attended " + band + 
                                "'s shows");
                    }
                    
                }
                catch (java.sql.SQLException e)
                {
                    System.out.print(e);
                    return;
                }
            }
            else
            {
                done = true;
            }
        }
    }
}
